package com.iot.inclinometer.dao.impl;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.iot.inclinometer.dao.IBaseInfoDao;
import com.iot.inclinometer.db.DBHelper;
import com.iot.inclinometer.pojo.BaseInfo;
import com.iot.inclinometer.pojo.CorrectDataInfo;
import com.iot.inclinometer.pojo.DataInfo;
import com.iot.inclinometer.pojo.LeijiInfo;
import com.iot.inclinometer.pojo.ObserveInfo;
import com.iot.inclinometer.pojo.RealInfo;

import java.util.ArrayList;
import java.util.List;

/**
 * 封装了对BaseInfo表的基本操作
 * @author ZSL
 */
public class BaseInfoDaoImpl implements IBaseInfoDao {
	private DBHelper helper;

	public BaseInfoDaoImpl(Context context) {
		helper = new DBHelper(context);
	}

	@Override
	public List<BaseInfo> selectBaseInfos() {
		List<BaseInfo> allBaseInfos = new ArrayList<BaseInfo>();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from base_info";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, null);
			while (cursor.moveToNext()) {

				int _id = cursor.getInt(cursor.getColumnIndex("base_id"));
				String _projectName = cursor.getString(cursor
						.getColumnIndex("project_name"));
				String _itemName = cursor.getString(cursor
						.getColumnIndex("item_name"));
				String _drillNum = cursor.getString(cursor
						.getColumnIndex("drill_num"));
				String _depth = cursor
						.getString(cursor.getColumnIndex("depth"));
				String _mark1 = cursor
						.getString(cursor.getColumnIndex("mark1"));
				String _mark2 = cursor
						.getString(cursor.getColumnIndex("mark2"));
				String _mark3 = cursor
						.getString(cursor.getColumnIndex("mark3"));
				String _mark4 = cursor
						.getString(cursor.getColumnIndex("mark4"));
				String _loc = cursor.getString(cursor.getColumnIndex("loc"));
				String _x = cursor.getString(cursor.getColumnIndex("x"));
				String _y = cursor.getString(cursor.getColumnIndex("y"));
				String _azimuth = cursor.getString(cursor.getColumnIndex("azimuth"));

				BaseInfo baseInfo = new BaseInfo();
				baseInfo.setBaseId(_id);
				baseInfo.setProjectName(_projectName);
				baseInfo.setItemName(_itemName);
				baseInfo.setDrillNum(_drillNum);
				baseInfo.setDepth(_depth);
				baseInfo.setMark1(_mark1);
				baseInfo.setMark2(_mark2);
				baseInfo.setMark3(_mark3);
				baseInfo.setMark4(_mark4);
				baseInfo.setLoc(_loc);
				baseInfo.setX(_x);
				baseInfo.setY(_y);
				baseInfo.setAzimuth(_azimuth);

				allBaseInfos.add(baseInfo);
				// Log.i("INFO", baseInfo.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return allBaseInfos;
	}

	@Override
	public List<BaseInfo> selectBaseInfoById(int baseId) {
		List<BaseInfo> allBaseInfo =new ArrayList<BaseInfo>();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from base_info where base_id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql,
					new String[] { Integer.toString(baseId) });
			while (cursor.moveToNext()) {
				int _id = cursor.getInt(cursor.getColumnIndex("base_id"));
				String _projectName = cursor.getString(cursor
						.getColumnIndex("project_name"));
				String _itemName = cursor.getString(cursor
						.getColumnIndex("item_name"));
				String _drillNum = cursor.getString(cursor
						.getColumnIndex("drill_num"));
				String _depth = cursor
						.getString(cursor.getColumnIndex("depth"));
				String _mark1 = cursor
						.getString(cursor.getColumnIndex("mark1"));
				String _mark2 = cursor
						.getString(cursor.getColumnIndex("mark2"));
				String _mark3 = cursor
						.getString(cursor.getColumnIndex("mark3"));
				String _mark4 = cursor
						.getString(cursor.getColumnIndex("mark4"));
				String _loc = cursor.getString(cursor.getColumnIndex("loc"));
				String _x = cursor.getString(cursor.getColumnIndex("x"));
				String _y = cursor.getString(cursor.getColumnIndex("y"));
				String _azimuth = cursor.getString(cursor.getColumnIndex("azimuth"));
				
				BaseInfo baseInfo = new BaseInfo();
				baseInfo.setBaseId(_id);
				baseInfo.setProjectName(_projectName);
				baseInfo.setItemName(_itemName);
				baseInfo.setDrillNum(_drillNum);
				baseInfo.setDepth(_depth);
				baseInfo.setMark1(_mark1);
				baseInfo.setMark2(_mark2);
				baseInfo.setMark3(_mark3);
				baseInfo.setMark4(_mark4);
				baseInfo.setLoc(_loc);
				baseInfo.setX(_x);
				baseInfo.setY(_y);
				baseInfo.setAzimuth(_azimuth);
				allBaseInfo.add(baseInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return allBaseInfo;
	}


	@Override
	public int add(BaseInfo baseInfo) {
		int _id = 0;
		SQLiteDatabase database = null;
		try {
			String sql1 = "INSERT INTO base_info(project_name,item_name,drill_num,depth,mark1,mark2,mark3,mark4,loc,x,y,azimuth) VALUES (?,?,?,?,?,?,?,?,?,?,?,?);";
			database = helper.getWritableDatabase();
			database.execSQL(
					sql1,
					new Object[] { baseInfo.getProjectName(),
							baseInfo.getItemName(), baseInfo.getDrillNum(),
							baseInfo.getDepth(), baseInfo.getMark1(),
							baseInfo.getMark2(), baseInfo.getMark3(),
							baseInfo.getMark4(), baseInfo.getLoc(),
							baseInfo.getX(), baseInfo.getY(),baseInfo.getAzimuth() });

			String sql2 = "select * from base_info where project_name=? and item_name=? and drill_num=?";

			Cursor cursor = database.rawQuery(
					sql2,
					new String[] { baseInfo.getProjectName(),
							baseInfo.getItemName(), baseInfo.getDrillNum() });
			while (cursor.moveToNext()) {
				_id = cursor.getInt(cursor.getColumnIndex("base_id"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		Log.i("info", "插入的数据的ID是：" + _id);
		return _id;
	}

	@Override
	public List<BaseInfo> selectBaseInfos(String projectName) {
		List<BaseInfo> allBaseInfos = new ArrayList<BaseInfo>();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from base_info where project_name = ?";
			database = helper.getReadableDatabase();
			Cursor cursor = database
					.rawQuery(sql, new String[] { projectName });
			while (cursor.moveToNext()) {

				int _id = cursor.getInt(cursor.getColumnIndex("base_id"));
				String _projectName = cursor.getString(cursor
						.getColumnIndex("project_name"));
				String _itemName = cursor.getString(cursor
						.getColumnIndex("item_name"));
				String _drillNum = cursor.getString(cursor
						.getColumnIndex("drill_num"));
				String _depth = cursor
						.getString(cursor.getColumnIndex("depth"));
				String _mark1 = cursor
						.getString(cursor.getColumnIndex("mark1"));
				String _mark2 = cursor
						.getString(cursor.getColumnIndex("mark2"));
				String _mark3 = cursor
						.getString(cursor.getColumnIndex("mark3"));
				String _mark4 = cursor
						.getString(cursor.getColumnIndex("mark4"));
				String _loc = cursor.getString(cursor.getColumnIndex("loc"));
				String _x = cursor.getString(cursor.getColumnIndex("x"));
				String _y = cursor.getString(cursor.getColumnIndex("y"));
				String _azimuth = cursor.getString(cursor.getColumnIndex("azimuth"));

				BaseInfo baseInfo = new BaseInfo();
				baseInfo.setBaseId(_id);
				baseInfo.setProjectName(_projectName);
				baseInfo.setItemName(_itemName);
				baseInfo.setDrillNum(_drillNum);
				baseInfo.setDepth(_depth);
				baseInfo.setMark1(_mark1);
				baseInfo.setMark2(_mark2);
				baseInfo.setMark3(_mark3);
				baseInfo.setMark4(_mark4);
				baseInfo.setLoc(_loc);
				baseInfo.setX(_x);
				baseInfo.setY(_y);
				baseInfo.setAzimuth(_azimuth);

				allBaseInfos.add(baseInfo);
				// Log.i("INFO", baseInfo.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return allBaseInfos;
	}

	@Override
	public List<BaseInfo> selectBaseInfos(String projectName, String itemName) {
		List<BaseInfo> allBaseInfos = new ArrayList<BaseInfo>();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from base_info where project_name = ? and item_name = ?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] { projectName,
					itemName });
			while (cursor.moveToNext()) {

				int _id = cursor.getInt(cursor.getColumnIndex("base_id"));
				String _projectName = cursor.getString(cursor
						.getColumnIndex("project_name"));
				String _itemName = cursor.getString(cursor
						.getColumnIndex("item_name"));
				String _drillNum = cursor.getString(cursor
						.getColumnIndex("drill_num"));
				String _depth = cursor
						.getString(cursor.getColumnIndex("depth"));
				String _mark1 = cursor
						.getString(cursor.getColumnIndex("mark1"));
				String _mark2 = cursor
						.getString(cursor.getColumnIndex("mark2"));
				String _mark3 = cursor
						.getString(cursor.getColumnIndex("mark3"));
				String _mark4 = cursor
						.getString(cursor.getColumnIndex("mark4"));
				String _loc = cursor.getString(cursor.getColumnIndex("loc"));
				String _x = cursor.getString(cursor.getColumnIndex("x"));
				String _y = cursor.getString(cursor.getColumnIndex("y"));
				String _azimuth = cursor.getString(cursor.getColumnIndex("azimuth"));

				BaseInfo baseInfo = new BaseInfo();
				baseInfo.setBaseId(_id);
				baseInfo.setProjectName(_projectName);
				baseInfo.setItemName(_itemName);
				baseInfo.setDrillNum(_drillNum);
				baseInfo.setDepth(_depth);
				baseInfo.setMark1(_mark1);
				baseInfo.setMark2(_mark2);
				baseInfo.setMark3(_mark3);
				baseInfo.setMark4(_mark4);
				baseInfo.setLoc(_loc);
				baseInfo.setX(_x);
				baseInfo.setY(_y);
				baseInfo.setAzimuth(_azimuth);

				allBaseInfos.add(baseInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return allBaseInfos;
	}

	@Override
	public BaseInfo selectBaseInfos(String projectName, String itemName,
			String drillNum) {
		BaseInfo baseInfo = new BaseInfo();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from base_info where project_name = ? and item_name = ? and drill_num = ?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] { projectName,
					itemName, drillNum });
			while (cursor.moveToNext()) {

				int _id = cursor.getInt(cursor.getColumnIndex("base_id"));
				String _projectName = cursor.getString(cursor
						.getColumnIndex("project_name"));
				String _itemName = cursor.getString(cursor
						.getColumnIndex("item_name"));
				String _drillNum = cursor.getString(cursor
						.getColumnIndex("drill_num"));
				String _depth = cursor
						.getString(cursor.getColumnIndex("depth"));
				String _mark1 = cursor
						.getString(cursor.getColumnIndex("mark1"));
				String _mark2 = cursor
						.getString(cursor.getColumnIndex("mark2"));
				String _mark3 = cursor
						.getString(cursor.getColumnIndex("mark3"));
				String _mark4 = cursor
						.getString(cursor.getColumnIndex("mark4"));
				String _loc = cursor.getString(cursor.getColumnIndex("loc"));
				String _x = cursor.getString(cursor.getColumnIndex("x"));
				String _y = cursor.getString(cursor.getColumnIndex("y"));
				String _azimuth = cursor.getString(cursor.getColumnIndex("azimuth"));

				baseInfo.setBaseId(_id);
				baseInfo.setProjectName(_projectName);
				baseInfo.setItemName(_itemName);
				baseInfo.setDrillNum(_drillNum);
				baseInfo.setDepth(_depth);
				baseInfo.setMark1(_mark1);
				baseInfo.setMark2(_mark2);
				baseInfo.setMark3(_mark3);
				baseInfo.setMark4(_mark4);
				baseInfo.setLoc(_loc);
				baseInfo.setX(_x);
				baseInfo.setY(_y);
				baseInfo.setAzimuth(_azimuth);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return baseInfo;
	}

	@Override
	public BaseInfo selectBaseInfo(String drillNum) {
		BaseInfo baseInfo = new BaseInfo();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from base_info where drill_num=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql,
					new String[] { drillNum});
			while (cursor.moveToNext()) {
				int _id = cursor.getInt(cursor.getColumnIndex("base_id"));
				String _projectName = cursor.getString(cursor
						.getColumnIndex("project_name"));
				String _itemName = cursor.getString(cursor
						.getColumnIndex("item_name"));
				String _drillNum = cursor.getString(cursor
						.getColumnIndex("drill_num"));
				String _depth = cursor
						.getString(cursor.getColumnIndex("depth"));
				String _mark1 = cursor
						.getString(cursor.getColumnIndex("mark1"));
				String _mark2 = cursor
						.getString(cursor.getColumnIndex("mark2"));
				String _mark3 = cursor
						.getString(cursor.getColumnIndex("mark3"));
				String _mark4 = cursor
						.getString(cursor.getColumnIndex("mark4"));
				String _loc = cursor.getString(cursor.getColumnIndex("loc"));
				String _x = cursor.getString(cursor.getColumnIndex("x"));
				String _y = cursor.getString(cursor.getColumnIndex("y"));
				String _azimuth = cursor.getString(cursor.getColumnIndex("azimuth"));

				baseInfo.setBaseId(_id);
				baseInfo.setProjectName(_projectName);
				baseInfo.setItemName(_itemName);
				baseInfo.setDrillNum(_drillNum);
				baseInfo.setDepth(_depth);
				baseInfo.setMark1(_mark1);
				baseInfo.setMark2(_mark2);
				baseInfo.setMark3(_mark3);
				baseInfo.setMark4(_mark4);
				baseInfo.setLoc(_loc);
				baseInfo.setX(_x);
				baseInfo.setY(_y);
				baseInfo.setAzimuth(_azimuth);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return baseInfo;
	}
	@Override
	public int deleteByBaseId(int baseId){
		//得到可写的SQLiteDatabase对象  
        SQLiteDatabase database = helper.getWritableDatabase();  
        //调用delete方法，删除数据  
        int flag=database.delete("base_info", "base_id=?", new String[]{Integer.toString(baseId)});  
        return flag;
	}
	
	
	/**
	 * 导入备份文件
	 */
	@Override
	public boolean insertList(List<BaseInfo> baseInfoList,List<ObserveInfo> observeInfoList,List<DataInfo> dataInfoList,List<LeijiInfo> leijidataInfoList,List<CorrectDataInfo> correctDataInfoList,List<RealInfo> realInfoList){
		boolean flag = false;
        SQLiteDatabase database= helper.getWritableDatabase();//得到可写的SQLiteDatabase对象
        database.beginTransaction();// 开始事务
        //删除表格
        String sql1= "DELETE FROM base_info";
        String sql2= "DELETE FROM observe_info";
        String sql3= "DELETE FROM data_info";
        String sql4= "DELETE FROM correct_data_info";
        String sql9= "DELETE FROM leiji_data_info";
        String sql11= "DELETE FROM sqlite_sequence";
        String sql12= "DELETE FROM real_data_info";
        database.execSQL(sql1);
        database.execSQL(sql2);
        database.execSQL(sql3);
        database.execSQL(sql4);
        database.execSQL(sql9);
        database.execSQL(sql11);
        database.execSQL(sql12);
		try {
			String sql5 = "INSERT INTO base_info(base_id,project_name,item_name,drill_num,depth,mark1,mark2,mark3,mark4,loc,x,y,azimuth) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);";
			String sql6 = "INSERT INTO observe_info(ob_id,observer,time,interval,measurecount,slipdeep,base_id,count) VALUES (?,?,?,?,?,?,?,?);";
			String sql7 = "INSERT INTO data_info(data_id,depth,lx,ly,error,theta_x,theta_y,theta,ob_id) VALUES (?,?,?,?,?,?,?,?,?);";	
			String sql8 = "INSERT INTO correct_data_info(data_id,depth,lx,ly,error,theta_x,theta_y,theta,ob_id,A0,B0,A180,B180) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);";
			String sql10 = "INSERT INTO leiji_data_info(leiji_id,depth,lx,ly,theta_x,theta_y,ob_id) VALUES (?,?,?,?,?,?,?);";
			String sql13 = "INSERT INTO real_data_info(real_id,theta_A,theta_B,ltheta_A,ltheta_B,lltheta_A,lltheta_B,currentMeasureTag,ob_id) VALUES (?,?,?,?,?,?,?,?,?);";	
			//自增的字段也可以直接赋值；sqlite_sequence表不用管，插入那四个表的数据之后，sqlite_sequence表自动生成了数据
			database = helper.getWritableDatabase();
			for(BaseInfo baseInfo:baseInfoList){
				database.execSQL(
						sql5,
						new Object[] { baseInfo.getBaseId(),baseInfo.getProjectName(),
								baseInfo.getItemName(), baseInfo.getDrillNum(),
								baseInfo.getDepth(), baseInfo.getMark1(),
								baseInfo.getMark2(), baseInfo.getMark3(),
								baseInfo.getMark4(), baseInfo.getLoc(),
								baseInfo.getX(), baseInfo.getY(),baseInfo.getAzimuth() });
			}
			for(ObserveInfo observeInfo:observeInfoList){
				database.execSQL(
						sql6,
						new Object[] {observeInfo.getObserveId(),observeInfo.getObserver(), 
								observeInfo.getTime(), observeInfo.getInterval(),
								observeInfo.getMeasurecount(), observeInfo.getSlipdeep(),
								observeInfo.getBaseId(), observeInfo.getCount() });
			}
			for(DataInfo dataInfo:dataInfoList){
				database.execSQL(
						sql7,
						new Object[] {dataInfo.getDataId(),dataInfo.getDepth(),
						dataInfo.getLx(),dataInfo.getLy(),dataInfo.getError(),dataInfo.getThetaX(),dataInfo.getThetaY(),
						dataInfo.getTheta(),dataInfo.getObserverId()});				
			}
			for(CorrectDataInfo dataInfo : correctDataInfoList) {
				database.execSQL(
						sql8,
						new Object[] {dataInfo.getDataId(),dataInfo.getDepth(),
						dataInfo.getLx(),dataInfo.getLy(),dataInfo.getError(),dataInfo.getThetaX(),dataInfo.getThetaY(),dataInfo.getTheta(),
						dataInfo.getObserverId(),dataInfo.getA0(),dataInfo.getB0(),dataInfo.getA180(),dataInfo.getB180()});
			}
			for(LeijiInfo dataInfo:leijidataInfoList){
				database.execSQL(
						sql10,
						new Object[] {dataInfo.getLeijiId(),dataInfo.getDepth(),
						dataInfo.getLx(),dataInfo.getLy(),dataInfo.getThetaX(),dataInfo.getThetaY(),
						dataInfo.getObserverId()});				
			}
			for(RealInfo realInfo:realInfoList){
				database.execSQL(
						sql13,
						new Object[] {realInfo.getRealId(),realInfo.getTheta_A(),
								realInfo.getTheta_B(),realInfo.getLtheta_A(),realInfo.getLtheta_B(),realInfo.getLltheta_A(),
								realInfo.getLltheta_B(),realInfo.getCurrentMeasureTag(),realInfo.getObserverId()});				
			}
			database.setTransactionSuccessful();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			database.endTransaction();
			if (database != null) {
				database.close();
			}
		}
        return flag;
	}
	@Override
	public int deleteAllBaseInfos() {
		//得到可写的SQLiteDatabase对象  
        SQLiteDatabase database = helper.getWritableDatabase();  
        String sql = "delete from base_info";
        database.execSQL(sql);
        database.close();
        //调用delete方法，删除数据  
        int flag=1;
        return flag;
	}
}
